/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.BuildingTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author AnhTuan
 */
public class BuildingTypesDAOImpl extends DatabaseConnection implements BuildingTypesDAO {

    public Iterator<BuildingTypes> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From BuildingTypes");
            while (rs.next()) {
                BuildingTypes row = new BuildingTypes();
                row.setBuildingTypeID(rs.getInt("BuildingTypeID"));
                row.setBuildingTypeName(rs.getString("TypeName"));

                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<BuildingTypes> getList(String buildingTypeName, int page) {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageBuildingTypes(?, ?, ?)}");
            cst.setString(1, buildingTypeName);
            cst.setInt(2, page);
            cst.setInt(3, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                BuildingTypes row = new BuildingTypes();
                row.setBuildingTypeID(rs.getInt("BuildingTypeID"));
                row.setBuildingTypeName(rs.getString("TypeName"));
                arr.add(row);
            }
            closeConnection();
            if (arr.size() > 0) {
                return arr.iterator();
            }
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int getPageCount(String buildingTypeName) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageBuildingTypes(?, ?)}");
            cst.setString(1, buildingTypeName);
            cst.setInt(2, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public BuildingTypes getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From BuildingTypes Where BuildingTypeID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            BuildingTypes row = new BuildingTypes();
            if (rs.next()) {
                row.setBuildingTypeID(rs.getInt("BuildingTypeID"));
                row.setBuildingTypeName(rs.getString("TypeName"));
            }

            closeConnection();
            return row;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return null;
    }

    public boolean checkDuplicateName(String name) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select BuildingTypeID From BuildingTypes Where TypeName=?");
            pst.setString(1, name);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }

    }

    public int add(BuildingTypes object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into BuildingTypes(TypeName) Values(?)");
            pst.setString(1, object.getBuildingTypeName());
            int result = pst.executeUpdate();
            closeConnection();

            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return -1;
    }

    public int update(BuildingTypes object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update BuildingTypes Set TypeName=? Where BuildingTypeID=?");
            pst.setString(1, object.getBuildingTypeName());
            pst.setInt(2, object.getBuildingTypeID());
            int result = pst.executeUpdate();
            closeConnection();

            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete BuildingTypes Where BuildingTypeID=? " +
                    "AND ? NOT IN (SELECT BuildingTypeID FROM Buildings)");
            pst.setInt(1, id);
            pst.setInt(2, id);
            int result = pst.executeUpdate();
            closeConnection();

            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return -1;
    }

    public int deleteIn(String arrayIds) {
        try {
            openConnection();
            String sql = "Delete BuildingTypes Where BuildingTypeID in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();

            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return -1;
    }
}
